/** 
Copyright (c) 2010, Sheikh Abdul Wahid Ahmed
Details @ http://extremecodeworld.codeplex.com/license
**/

using System;
using EXtremecode.Utils;
using System.Data;
using System.Data.Common; 
using System.Data.OracleClient;
using EXtremecode.DataAccessLayer; 
using System.Collections;
using System.Text;
using System.Text.RegularExpressions;
using EXtremecode.Common;
using System.Collections.Generic;
using System.Data.OleDb;

namespace EXtremecode.DataAccessLayer
{
	/// <summary>
	/// Summary description for SQLServerDataAccessor.
	/// </summary>
	public class OracleDataAccessor:DataAccessor
	{
        private string userId = string.Empty;

        #region SYSTEM QUERIES
		internal override string QUERY_Table 
		{ 
			get 
			{
                return string.Format(@"select OBJECT_NAME as Name from all_objects 
                    where Upper(owner) = Upper('{0}') and Upper(OBJECT_TYPE) = Upper('TABLE')"
                    ,userId);
			}
		}
        internal override string QUERY_View 
		{
			get 
			{
                return string.Format(@"select OBJECT_NAME as Name from all_objects 
                    where Upper(owner) = Upper('{0}') and Upper(OBJECT_TYPE) = Upper('VIEW')"
                    , userId);
			}
		}
		internal override string QUERY_TableColumn 
		{
			get 
			{
				return string.Format(@"select TABLE_NAME as Table_Name, COLUMN_NAME as ColumnName, 1 as TypeCode, DATA_LENGTH as Length
                        from all_tab_columns where owner = '{0}'",userId);
			}
		}
		#endregion

        protected override DataTable GetRelationInfo()
        {
            string connectionString = string.Format("Provider=MSDAORA;{0}"
                , m_sConnectionURL);


            DataTable dtRelationsInfo = null;
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                try
                {
                    dtRelationsInfo = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys,
                        new object[] { });
                }
                finally
                {
                    conn.Close();
                }
            }


            dtRelationsInfo.DefaultView.RowFilter = 
                string.Format(@"PK_TABLE_SCHEMA = '{0}' and 
                    FK_TABLE_SCHEMA = '{0}'",userId);
            dtRelationsInfo = dtRelationsInfo.DefaultView.ToTable();

            return dtRelationsInfo;

        }
		
		

        public override string DBNow
        {
            get { return "sysdate"; }
        }

       		public OracleDataAccessor(string sAccessorName,string sConnectionURL):this(sAccessorName,sConnectionURL,true)
		{
			
		}

        public OracleDataAccessor(string sAccessorName, string sConnectionURL, bool validationRequired)
            : base(sAccessorName, sConnectionURL,validationRequired)
        {
            userId = StringProcessor.GetValueFromConnetionString(sConnectionURL, "User ID");
        }
        
        		
		
		public override string GetParameterName(string paramName)
		{
			return ":" + paramName;
		}
		public override string GetEnclosedDbObject(string dbObjectName)
		{
			return string.Format("{0}",dbObjectName);
		}
		public override IDbCommand CreateCommand()
		{
			return new OracleCommand(); 
		}

        internal override IDbDataAdapter CreateDataAdapter()
		{
            OracleDataAdapter adapter = new OracleDataAdapter();
			adapter.RowUpdating += new OracleRowUpdatingEventHandler(Adapter_RowUpdating);
			adapter.RowUpdated  += new OracleRowUpdatedEventHandler(Adapter_RowUpdated);
			return  adapter;
		}

		public override IDataParameter GetParameter(string name,string type)
		{
			OracleParameter param=new OracleParameter(name,(OracleType)int.Parse(FreeHash.OracleDBType[type]),0); 
			return param;
		}
		

		
		protected override IDbConnection  CreateConnection()
		{
			OracleConnection conn=new OracleConnection(m_sConnectionURL);
			conn.StateChange +=new StateChangeEventHandler(base.Connection_StateChange);
			return conn;

		}
        public override IEnumerable<IDbCommand> GetInternalCommands(IDbCommand mainCommand)
        {
            return GetInternalCommands(mainCommand, true);
        }
        private IEnumerable<IDbCommand> GetInternalCommands(IDbCommand mainCommand, bool returnMainCommandItself)
        {

            if (returnMainCommandItself)
            {
                try
                {
                    mainCommand.CommandText = mainCommand.CommandText.Replace(";", "--;");
                    yield return mainCommand;
                }
                finally
                {
                    mainCommand.CommandText = mainCommand.CommandText.Replace("--;", ";");
                }
            }


            string[] currentSelectCommandsText = mainCommand.CommandText.Split(';');
            if (currentSelectCommandsText.Length > 1
                && currentSelectCommandsText[1].Trim() != string.Empty)
            {
                //we have only maximum two command in single statement.
                //if second command is defined then create its command object and return it to caller. 


                IDbCommand cmdTemp = (mainCommand is ServiceCommand) ?
                    (mainCommand as ServiceCommand).Accessor.CreateCommand() :
                    CreateCommand();

                cmdTemp.CommandText = currentSelectCommandsText[1]; //second command; 
                cmdTemp.Connection = mainCommand.Connection;
                cmdTemp.Transaction = mainCommand.Transaction;
               
                foreach (IDbDataParameter param in mainCommand.Parameters)
                {
                    //we are considering, where clause of this comand contain the reference of key params
                    //so we are add these param into new created command.

                    if (param.ParameterName.EndsWith(ColumnSuffix.KeyParam))
                    {
                        IDbDataParameter newParam = cmdTemp.CreateParameter();
                        Utils.DataAccessorUtil.MakeCloneOfParameter(param, newParam);
                        cmdTemp.Parameters.Add(newParam);
                    }
                    else
                    {
                        break; //only top parameter are expected as a key param. 
                    }
                }

                yield return cmdTemp;

            }



            yield break;

        }

		private void Adapter_RowUpdating(object sender, OracleRowUpdatingEventArgs e)
        {
             base.Adapter_RowUpdating(sender, e);
             e.Command.CommandText = e.Command.CommandText.Replace(";", "--;");
           
		}

        private void Adapter_RowUpdated(object sender, OracleRowUpdatedEventArgs e)
        {
            base.Adapter_RowUpdated(sender, e);

            e.Command.CommandText = e.Command.CommandText.Replace("--;", ";");
            IDbDataAdapter da = sender as IDbDataAdapter;


            if ((e.Command == da.UpdateCommand || e.Command == da.InsertCommand) &&
                e.Errors == null)
            {
                //should be update or insert command
                //no error should be occoured

                foreach (IDbCommand cmd in GetInternalCommands(e.Command, false))
                {
                    using (OracleDataAdapter daTemp = new OracleDataAdapter())
                    {
                        daTemp.SelectCommand = (OracleCommand)cmd ;
                        using (DataTable dt = new DataTable())
                        {
                            daTemp.Fill(dt);
                            if (dt.Rows.Count > 0)
                            {
                                DBUtil.CopyDataRow(dt.Rows[0], e.Row);
                            }
                        }
                    }
                    break;

                }

            }

        }


       

		
		internal override string GeneratePagingQuery(string selectQuery,string sortedBy,bool isSortingTypeAscending,int pageNumber, int pageSize,long? totalRecords)
		{
			if(sortedBy.Trim()==string.Empty)
			{
				throw new Exception("[UC] Sorted Column should be defined");
			}

			string sortingType=(isSortingTypeAscending)? " ASC ": " DESC ";
            long bottomRowNum = pageNumber*pageSize;
            long topRowNum = bottomRowNum - pageSize + 1;

            //original query with order by.
            string firstQuery = string.Format("{0} order by {1} ",
                selectQuery,
                sortedBy.Replace(",", sortingType + ",") + sortingType);

			//enclose first query;
			string secondQuery = string.Format(@"Select * from 
                (
                    select query1.*,rownum as row_num from
                    (
                        {0}
                    ) query1
                ) where row_num >= {1} and row_num <= {2}",
				firstQuery,topRowNum,bottomRowNum);


            return secondQuery; 
		}
	}
}
